package com.yict.csms.system.dao.impl;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.stereotype.Repository;

import com.yict.common.dao.impl.BaseDao;
import com.yict.csms.system.dao.IProfessionDao;
import com.yict.csms.system.entity.Profession;

@Repository("professionDao")
public class ProfessionDaoImpl extends BaseDao<Profession,Long> implements IProfessionDao {
	
	/**
	 * 查询某个公司在指定日期有合同的工种
	 * @param companyid 公司主键
	 * @param contractDate 合同日期
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Profession> queryByCompany(final Long companyid,final Date contractDate){
		List<Profession> list;
		StringBuilder hql = new StringBuilder("select c.profession from Contract t inner join t.contractRateSet c");
		hql.append(" where t.company.companyId = :companyId");
		hql.append(" and c.effectiveDate <= :contractDate and c.expirationDate >= :contractDate");
		hql.append(" and c.ratestutas=:ratestutas");
		final String queryStr = hql.toString();	
		list = (List<Profession>)this.getHibernateTemplate().execute(new HibernateCallback() {
			
			@Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				Query query = session.createQuery(queryStr);
				query.setParameter("companyId", companyid);
				query.setParameter("contractDate", contractDate);
				query.setParameter("ratestutas", "Y");
				return query.list();
			}
		});
		if(list == null) list = new ArrayList<Profession>();
		return list;
	}
}
